package com.dailyblue.smbms.dao;

import com.dailyblue.smbms.bean.User;
import com.dailyblue.smbms.db.DBManager;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    @SneakyThrows
    public List<User> find(int page) {
        List<User> list = new ArrayList<User>();
        int start = (page - 1) * 5;
        String sql = "select u.*,floor(DATEDIFF(now(),birthday)/365) from smbms_user u  where state=1 limit ?,5";
        conn = DBManager.getConnection();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, start);
        rs = ps.executeQuery();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getInt(1));
            user.setUserCode(rs.getString(2));
            user.setUserName(rs.getString(3));
            user.setUserPassword(rs.getString(4));
            user.setGender(rs.getInt(5));
            user.setBirthday(rs.getString(6));
            user.setPhone(rs.getString(7));
            user.setAddress(rs.getString(8));
            user.setUserRole(rs.getInt(9));
            user.setCreatedBy(rs.getInt(10));
            user.setCreationDate(rs.getString(11));
            user.setModifyBy(rs.getInt(12));
            user.setModifyDate(rs.getString(13));
            user.setUserAge(rs.getInt(15));
            list.add(user);
        }
        return list;
    }

    @SneakyThrows
    public User findById(int id) {
        String sql = "select u.*,floor(DATEDIFF(now(),birthday)/365) from smbms_user u where id=? and state=1";
        conn = DBManager.getConnection();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        rs = ps.executeQuery();
        if (rs.next()) {
            User user = new User();
            user.setId(rs.getInt(1));
            user.setUserCode(rs.getString(2));
            user.setUserName(rs.getString(3));
            user.setUserPassword(rs.getString(4));
            user.setGender(rs.getInt(5));
            user.setBirthday(rs.getString(6));
            user.setPhone(rs.getString(7));
            user.setAddress(rs.getString(8));
            user.setUserRole(rs.getInt(9));
            user.setCreatedBy(rs.getInt(10));
            user.setCreationDate(rs.getString(11));
            user.setModifyBy(rs.getInt(12));
            user.setModifyDate(rs.getString(13));
            user.setUserAge(rs.getInt(15));
            return user;
        }
        return null;
    }

    @SneakyThrows
    public List<User> findByNameAndRole(String lastSql) {
        List<User> list = new ArrayList<User>();
        String sql = "select u.*,floor(DATEDIFF(now(),birthday)/365) from smbms_user u " + lastSql;
        conn = DBManager.getConnection();
        ps = conn.prepareStatement(sql);
        System.out.println(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getInt(1));
            user.setUserCode(rs.getString(2));
            user.setUserName(rs.getString(3));
            user.setUserPassword(rs.getString(4));
            user.setGender(rs.getInt(5));
            user.setBirthday(rs.getString(6));
            user.setPhone(rs.getString(7));
            user.setAddress(rs.getString(8));
            user.setUserRole(rs.getInt(9));
            user.setCreatedBy(rs.getInt(10));
            user.setCreationDate(rs.getString(11));
            user.setModifyBy(rs.getInt(12));
            user.setModifyDate(rs.getString(13));
            user.setUserAge(rs.getInt(15));
            list.add(user);
        }
        return list;
    }

    @SneakyThrows
    public int findSearchCount(String lastSql) {
        String sql = "select count(*) from smbms_user " + lastSql;
        conn = DBManager.getConnection();
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        if (rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    }

    @SneakyThrows
    public int findAllCount() {
        String sql = "select count(*) from smbms_user where state=1";
        conn = DBManager.getConnection();
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        if (rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    }

    @SneakyThrows
    public void save(User user) {
        String sql = "insert into smbms_user values(null,?,?,md5(?),?,?,?,?,?,null,now(),null,null,1)";
        conn = DBManager.getConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, user.getUserCode());
        ps.setString(2, user.getUserName());
        ps.setString(3, user.getUserPassword());
        ps.setInt(4, user.getGender());
        ps.setString(5, user.getBirthday());
        ps.setString(6, user.getPhone());
        ps.setString(7, user.getAddress());
        ps.setInt(8, user.getUserRole());
        ps.executeUpdate();
    }

    @SneakyThrows
    public boolean checkIsUnique(String field, String value) {
        String sql = "select count(*) from smbms_user where " + field + "=?";
        conn = DBManager.getConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, value);
        rs = ps.executeQuery();
        if (rs.next()) {
            int row = rs.getInt(1);
            return row == 0 ? true : false;
        }
        return false;
    }

    @SneakyThrows
    public void removeById(int id) {
        String sql = "update smbms_user set state=0 where id=?";
        conn = DBManager.getConnection();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        ps.executeUpdate();
    }

    @SneakyThrows
    public void update(User user) {
        String sql = "update smbms_user set userName=?,gender=?,birthday=?,phone=?,address=?,userRole=?,modifyDate=now() where id=?";
        conn = DBManager.getConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, user.getUserName());
        ps.setInt(2, user.getGender());
        ps.setString(3, user.getBirthday());
        ps.setString(4, user.getPhone());
        ps.setString(5, user.getAddress());
        ps.setInt(6, user.getUserRole());
        ps.setInt(7, user.getId());
        ps.executeUpdate();
    }

    @SneakyThrows
    public void updatePass(int id, String newPass) {
        String sql = "update smbms_user set userPassword=md5(?) where id=?";
        conn = DBManager.getConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, newPass);
        ps.setInt(2, id);
        ps.executeUpdate();
    }

    @SneakyThrows
    public User login(String code, String password) {
        String sql = "select * from smbms_user where state=1 and userCode=? and userPassword=md5(?)";
        conn = DBManager.getConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, code);
        ps.setString(2, password);
        rs = ps.executeQuery();
        if (rs.next()) {
            User user = new User();
            user.setId(rs.getInt(1));
            user.setUserCode(rs.getString(2));
            user.setUserName(rs.getString(3));
            user.setUserPassword(rs.getString(4));
            user.setGender(rs.getInt(5));
            user.setBirthday(rs.getString(6));
            user.setPhone(rs.getString(7));
            user.setAddress(rs.getString(8));
            user.setUserRole(rs.getInt(9));
            user.setCreatedBy(rs.getInt(10));
            user.setCreationDate(rs.getString(11));
            user.setModifyBy(rs.getInt(12));
            user.setModifyDate(rs.getString(13));
            return user;
        }
        return null;
    }

    @SneakyThrows
    public boolean checkPassIsRight(int id, String oldPass) {
        String sql = "select count(*) from smbms_user where id=? and userPassword=md5(?)";
        conn = DBManager.getConnection();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        ps.setString(2, oldPass);
        rs = ps.executeQuery();
        if (rs.next()) {
            if (rs.getInt(1) > 0) {
                return true;
            }
        }
        return false;
    }
}